﻿using System;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using NLite.Collections;
using NLite.Data.Driver;
using System.Data;
using NLite.Data.Schema.Internal;

namespace NLite.Data.Schema
{
    public interface ISchemaProvider
    {

        IEnumerable<string> GetDatabases(DbConnection conn);
        IDataSourceInformation GetDataSourceInformation(DbConnection conn);
        IEnumerable<string> GetReservedWords(DbConnection conn);
        IEnumerable<string> GetTables(DbConnection conn, string database);
        IEnumerable<string> GetViews(DbConnection conn, string database);
        IEnumerable<IColumnSchema> GetColumns(DbConnection conn, string database, string tableName);
    }

    public interface ISchemaManager
    {
        IDbSchema Database { get; }
        IDbSchema[] Databases { get; }
        IDataSourceInformation DataSourceInfo { get; }
        string[] Keywords { get; }
        string[] Collections { get; }
    }

    public interface IDataSourceInformation
    {
        string CompositeIdentifierSeparatorPattern { get; }
        string DataSourceProductName { get; }
        string DataSourceProductVersion { get; }
        string DataSourceProductVersionNormalized { get; }
        System.Data.Common.GroupByBehavior GroupByBehavior { get; }
        System.Data.Common.IdentifierCase IdentifierCase { get; }
        string IdentifierPattern { get; }
        bool OrderByColumnsInSelect { get; }
        string ParameterMarkerFormat { get; }
        string ParameterMarkerPattern { get; }
        int ParameterNameMaxLength { get; }
        string ParameterNamePattern { get; }
        System.Data.Common.IdentifierCase QuotedIdentifierCase { get; }
        string QuotedIdentifierPattern { get; }
        string StatementSeparatorPattern { get; }
        string StringLiteralPattern { get; }
        System.Data.Common.SupportedJoinOperators SupportedJoinOperators { get; }
    }

    public interface IDbSchema
    {
        string Name { get; }
        void Refresh();
        ITableSchema[] Tables { get; }
        ITableSchema[] Views { get; }
    }

    public interface ITableSchema
    {
        IDbSchema Database { get; }
        string Name { get; }
        IColumnSchema[] Columns { get; }
        //PrimaryKeySchema PrimaryKey { get; }
        void Refresh();
    }

    public interface IColumnSchema
    {
        bool IsPrimaryKey { get; }
        bool IsUnique { get; }
        bool IsNullable { get; }
        string DataType { get; }
        string Name { get; }
        int? Size { get; }
    }

    public abstract class SchemaProvider : ISchemaProvider
    {
        public IDataSourceInformation GetDataSourceInformation(DbConnection conn)
        {
            return conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation).ToList<DataSourceInformation>().FirstOrDefault();
        }

        public IEnumerable<string> GetReservedWords(DbConnection conn)
        {
            return conn
                        .GetSchema(DbMetaDataCollectionNames.ReservedWords)
                        .Rows
                        .Cast<DataRow>()
                        .Select(p => p[0] as string)
                        .OrderBy(p => p);
        }

        protected virtual string DatabasesSchemaName
        {
            get { return "Databases"; }
        }

        public virtual IEnumerable<string> GetDatabases(DbConnection conn)
        {
            return InternalGetDatabases(conn)
                        .Rows
                        .Cast<DataRow>()
                        .Select(p => p[0] as string)
                        .OrderBy(p => p);
        }

        protected virtual DataTable InternalGetDatabases(DbConnection conn)
        {
            var tb = conn
                        .GetSchema(DatabasesSchemaName);
            return tb;
        }

        public IEnumerable<string> GetTables(DbConnection conn, string database)
        {
            return
                       GetSchemaTables(conn, database)
                       .Rows
                       .Cast<DataRow>()
                       .Select(p => p["TableName"] as string)
                       .OrderBy(p => p);
        }

        protected abstract DataTable GetSchemaTables(DbConnection conn, string database);
        protected abstract DataTable GetSchemaViews(DbConnection conn, string database);
        protected abstract DataTable GetSchemaColumns(DbConnection conn, string database, string tableName);

        public IEnumerable<string> GetViews(DbConnection conn, string database)
        {
            return
                        GetSchemaViews(conn, database)
                        .Rows
                        .Cast<DataRow>()
                        .Select(p => p[0] as string)
                        .OrderBy(p => p);
        }

        public IEnumerable<IColumnSchema> GetColumns(DbConnection conn, string database, string tableName)
        {
            var tb = GetSchemaColumns(conn, database, tableName);
            if (tb.Columns.Count > 0)
                tb.Columns["ColumnName"].ColumnName = "Name";
            return tb.ToList<ColumnSchema>().Cast<IColumnSchema>();
        }
    }

    namespace Internal
    {
        class DataSourceInformation : IDataSourceInformation
        {
            public string CompositeIdentifierSeparatorPattern { get; internal set; }
            public string DataSourceProductName { get; internal set; }
            public string DataSourceProductVersion { get; internal set; }
            public string DataSourceProductVersionNormalized { get; internal set; }
            public GroupByBehavior GroupByBehavior { get; internal set; }
            public string IdentifierPattern { get; internal set; }
            public IdentifierCase IdentifierCase { get; internal set; }
            public bool OrderByColumnsInSelect { get; internal set; }
            public string ParameterMarkerFormat { get; internal set; }
            public string ParameterMarkerPattern { get; internal set; }
            public int ParameterNameMaxLength { get; internal set; }
            public string ParameterNamePattern { get; internal set; }

            public string QuotedIdentifierPattern { get; internal set; }
            public IdentifierCase QuotedIdentifierCase { get; internal set; }
            public string StatementSeparatorPattern { get; internal set; }
            public string StringLiteralPattern { get; internal set; }
            public SupportedJoinOperators SupportedJoinOperators { get; internal set; }

        }

        class ColumnSchema : IColumnSchema
        {
            public bool IsUnique { get; internal set; }
            public bool IsPrimaryKey { get; internal set; }
            public bool IsNullable { get; internal set; }
            public string Name { get; internal set; }
            public string DataType { get; internal set; }
            public int? Size { get; internal set; }
        }

        class ForeignKeySchema
        {
            public string Name { get; internal set; }
            internal List<ColumnSchema> columns;

            public ColumnSchema[] Columns
            {
                get { return columns.ToArray(); }
            }
        }

        class PrimaryKeySchema
        {
            public string Name { get; internal set; }
            internal List<IColumnSchema> columns;

            public IColumnSchema[] Columns
            {
                get { return columns.ToArray(); }
            }
        }

        class IndexSchema
        {
            public string Name { get; internal set; }
            internal List<IColumnSchema> columns;

            public IColumnSchema[] Columns
            {
                get { return columns.ToArray(); }
            }
        }

        class DbSchema : IDbSchema
        {
            public string Name { get; private set; }
            public ITableSchema[] Tables { get { return tables.Value; } }
            public ITableSchema[] Views { get { return views.Value; } }

            internal Lazy<ITableSchema[]> tables;
            internal Lazy<ITableSchema[]> views;

            internal DbSchema(string name)
            {
                Name = name;
            }

            public void Refresh()
            {
                tables.Refresh();
                views.Refresh();
            }

        }

        class TableSchema : ITableSchema
        {
            public string Name { get; private set; }
            public IDbSchema Database { get; private set; }
            public IColumnSchema[] Columns
            {
                get
                {
                    return columns.Value;
                }
            }
            public PrimaryKeySchema PrimaryKey { get; internal set; }

            private Lazy<IColumnSchema[]> columns;

            internal TableSchema(string name, IDbSchema db, Lazy<IColumnSchema[]> columns)
            {
                Name = name;
                Database = db;
                this.columns = columns;
            }

            public void Refresh()
            {
                columns.Refresh();
            }
        }

        class SchemaManager : ISchemaManager
        {
            private IDriver Driver;
            private ISchemaProvider Loader;

            public string[] Keywords { get; private set; }
            public string[] Collections { get; private set; }

            public IDataSourceInformation DataSourceInfo { get; private set; }
            public IDbSchema[] Databases { get; private set; }
            private string CurrentDatabaseName;

            public IDbSchema Database
            {
                get
                {
                    return Databases.FirstOrDefault(p => string.Equals(p.Name, CurrentDatabaseName, StringComparison.InvariantCultureIgnoreCase)); 
                }
            }

            internal DbConnection GetConnection()
            {
                var conn = Driver.CreateConnection();
                conn.Open();
                CurrentDatabaseName = conn.Database;
                return conn;
            }

            public SchemaManager(IDriver driver)
            {
                Driver = driver;
                Loader = driver.SchemaProvider;

                var databases = new List<DbSchema>();
                using (var conn = GetConnection())
                {
                    Keywords = Loader.GetReservedWords(conn).ToArray();
                    DataSourceInfo = Loader.GetDataSourceInformation(conn);
                    Collections = conn.GetSchema(DbMetaDataCollectionNames.MetaDataCollections).Rows.Cast<DataRow>().Select(p => p[0] as string).ToArray();
 
                    foreach (var databaseName in Loader.GetDatabases(conn))
                        databases.Add(GetDatabase(databaseName));
                    if (databases.Count == 0)
                        databases.Add(GetDatabase(conn.Database));

                    Databases = databases.ToArray();
                }
            }

            private DbSchema GetDatabase(string dbName)
            {

                DbSchema db = new DbSchema(dbName);

                db.tables = new Lazy<ITableSchema[]>(() =>
                {
                    var tables = new List<TableSchema>();
                    foreach (var tableName in Loader.GetTables(GetConnection(), dbName))
                        tables.Add(new TableSchema(tableName, db, new Lazy<IColumnSchema[]>(() =>
                        {
                            using (var conn = GetConnection())
                                return Loader.GetColumns(conn, dbName, tableName).ToArray();
                        })));

                    return tables.ToArray();
                });

                db.views = new Lazy<ITableSchema[]>(() =>
                {
                    var views = new List<ITableSchema>();
                    foreach (var viewName in Loader.GetViews(GetConnection(), dbName))
                        views.Add(new TableSchema(viewName, db, new Lazy<IColumnSchema[]>(() =>
                        {
                            using (var conn = GetConnection())
                                return Loader.GetColumns(conn, dbName, viewName).ToArray();
                        })));

                    return views.ToArray();
                });

                return db;
            }
        }
    }

    namespace Provider.Internal
    {
        class SQLiteSchemaProvider : SchemaProvider
        {
            public override IEnumerable<string> GetDatabases(DbConnection conn)
            {
                return new string[0];
            }

            protected override System.Data.DataTable GetSchemaTables(DbConnection conn, string database)
            {
                return conn.GetSchema("Tables");
            }

            protected override System.Data.DataTable GetSchemaViews(DbConnection conn, string database)
            {
                return conn.GetSchema("Views");
            }

            private DataTable tbColumns;

            protected override DataTable GetSchemaColumns(DbConnection conn, string database, string tableName)
            {
                if (tbColumns == null)
                {
                    tbColumns = conn.GetSchema("Columns");

                   
                }

                var rows = tbColumns.Rows.Cast<DataRow>().Where(p=>string.Equals(p["TableName"] , tableName));
                var tb = tbColumns.Clone();
                tb.Clear();
                rows.ForEach(p=>tb.Rows.Add(p.ItemArray));

                return tb;
            }
        }

        class OracleSchemaProvider : SchemaProvider
        {
            protected override string DatabasesSchemaName
            {
                get
                {
                    return "Users";
                }
            }

            protected override System.Data.DataTable GetSchemaTables(System.Data.Common.DbConnection conn, string database)
            {
                var tb = conn.GetSchema("Tables", new string[2] { database, null });
                return tb;
            }

            protected override DataTable GetSchemaViews(System.Data.Common.DbConnection conn, string database)
            {
                var tb = conn.GetSchema("Views", new string[2] { database, null });
                return tb;
            }

            protected override DataTable GetSchemaColumns(System.Data.Common.DbConnection conn, string database, string tableName)
            {
                var tb = conn.GetSchema("Columns", new string[3] { database, tableName, null });
                if (tb.Columns.Count > 0)
                {
                   
                    tb.Columns["Length"].ColumnName = "Size";
                    tb.Columns["Id"].ColumnName = "Order";
                    tb.Columns.Add(new DataColumn("IsNullable", typeof(bool)));

                    if (tb.Rows.Count > 0)
                    {
                        tb.Rows.Cast<DataRow>().ForEach(row => row["IsNullable"] = (row["Nullable"] as string) == "Y" ? true : false); 
                    }

                }

                tb.Columns.Remove("Nullable");
                return tb;
            }
        }

     

        class AccessSchemaProvider : SchemaProvider
        {
            protected override DataTable GetSchemaTables(System.Data.Common.DbConnection conn, string database)
            {
                var tb = conn.GetSchema("Tables", new string[4] { null, null, null, "TABLE" });
                tb.WriteXml(Console.Out);
                return tb;
            }

            protected override DataTable GetSchemaViews(DbConnection conn, string database)
            {
                var tb = conn.GetSchema("Views");
                return tb;
            }

            protected override DataTable GetSchemaColumns(DbConnection conn, string database, string tableName)
            {
                var tb = conn.GetSchema("Columns", new string[] { null, null, tableName, null });
                return tb;
            }

            public override IEnumerable<string> GetDatabases(DbConnection conn)
            {
                return new string[0];
            }

        }


        class MySqlSchemaProvider : SchemaProvider
        {
            protected override DataTable InternalGetDatabases(DbConnection conn)
            {
                var tb = base.InternalGetDatabases(conn);
                tb.Columns.RemoveAt(0);
                return tb;
            }
            protected override DataTable GetSchemaTables(DbConnection conn, string database)
            {
                var tb = conn.GetSchema("Tables", new string[4] { null, database, null, null });
                return tb;
            }

            protected override DataTable GetSchemaViews(DbConnection conn, string database)
            {
                var tb = conn.GetSchema("Views", new string[3] { null, database, null });
                return tb;
            }

            protected override DataTable GetSchemaColumns(DbConnection conn, string database, string tableName)
            {
                var tb = conn.GetSchema("Columns", new string[4] { null, database, tableName, null });
                tb.Columns.Add(new DataColumn("Nullable", typeof(bool)));

                if (tb.Rows.Count > 0)
                {
                    tb.Rows.Cast<DataRow>().ForEach(row => row["Nullable"] = (row["IsNullable"] as string) == "YES" ? true : false);
                }
                var col = tb.Columns["IsNullable"];
                tb.Columns.Remove(col);

                tb.Columns["Nullable"].ColumnName = "IsNullable";

                return tb;
            }
        }

        class MsSql2000SchemaProvider : SchemaProvider
        {
            protected override DataTable GetSchemaTables(DbConnection conn, string database)
            {
                throw new NotImplementedException();
            }

            protected override DataTable GetSchemaViews(DbConnection conn, string database)
            {
                throw new NotImplementedException();
            }

            protected override DataTable GetSchemaColumns(DbConnection conn, string database, string tableName)
            {
                throw new NotImplementedException();
            }
        }

        class MsSql2005SchemaProvider : MsSql2000SchemaProvider
        {
        }
    }
}
